<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * Description of sekolah_model
 *
 * @author Hindarwan
 */
class Sekolah_model extends CI_Model {

    var $id_sekolah = '';
    var $nama_sekolah = '';
    var $jumlah_toilet = '';

    function __construct() {
        parent::__construct();
    }

    function get_sekolah() {
        $query = $this->db->get('sekolah');
        return $query;
    }

    function insert() {
//        $this->id_sekolah = $this->input->post('id_sekolah');
        $this->nama_sekolah = $this->input->post('nama_sekolah');
        $this->jumlah_toilet = $this->input->post('jumlah_toilet');

        $this->db->insert('sekolah', $this);
    }

    function update($id) {

        $this->id_sekolah = $id;
        $this->nama_sekolah = $this->input->post('nama_sekolah');
        $this->jumlah_toilet = $this->input->post('jumlah_toilet');

        $this->db->where('id', $id);
        $this->db->update('sekolah', $this);
    }

    function delete($id) {
        $this->db->where('id', $id);
        $this->db->delete('sekolah');
    }
    
    //another function
    function get_sekolah_sorted() {
        $query = $this->db->query('SELECT nama_sekolah, jumlah_toilet, COUNT(*) as jumlah_laporan FROM laporan, sekolah, user WHERE laporan.id_user=user.id_user AND user.id_sekolah=sekolah.id_sekolah GROUP BY nama_sekolah, jumlah_toilet UNION SELECT nama_sekolah, jumlah_toilet, 0 FROM laporan, sekolah WHERE sekolah.id_sekolah NOT IN (SELECT sekolah.id_sekolah FROM laporan, sekolah, user WHERE laporan.id_user=user.id_user AND user.id_sekolah=sekolah.id_sekolah) ORDER BY jumlah_laporan DESC');
        return $query;
    }
    
    function get_sekolah_unsorted() {
        $query = $this->db->query('SELECT nama_sekolah, jumlah_toilet, COUNT(*) as jumlah_laporan FROM laporan, sekolah, user WHERE laporan.id_user=user.id_user AND user.id_sekolah=sekolah.id_sekolah GROUP BY nama_sekolah, jumlah_toilet UNION SELECT nama_sekolah, jumlah_toilet, 0 FROM laporan, sekolah WHERE sekolah.id_sekolah NOT IN (SELECT sekolah.id_sekolah FROM laporan, sekolah, user WHERE laporan.id_user=user.id_user AND user.id_sekolah=sekolah.id_sekolah)');
        return $query;
    }
    
    function get_sekolah_max() {
        $query = $this->db->query('SELECT nama_sekolah, jumlah_toilet, MAX(*) as jumlah_laporan FROM laporan, sekolah, user WHERE laporan.id_user=user.id_user AND user.id_sekolah=sekolah.id_sekolah GROUP BY nama_sekolah, jumlah_toilet ORDER BY jumlah_laporan DESC');
        return $query;
    }
}

?>
